﻿using System;
using System.Data;
using System.Data.SqlClient;
using AgnisDAL.ServiceAgnis;
using AgnisEbmtTranslator.DataSets;
using AgnisEbmtTranslator.DataSets.MappingDataSetTableAdapters;

namespace AgnisEbmtTranslator
{
    /// <summary>
    ///   Provides methods to work with the mapping information data.
    /// </summary>
    public static class MappingInfoDAL
    {
        #region local members

        private static String currentMappingInfoConnectionString;
        private static SqlConnection mySqlConnection;
        // Mapping information table adapters.
        private static readonly FieldValuesTableAdapter taMappingInfo = new FieldValuesTableAdapter();
        private static readonly RecordsSelectionTableAdapter taRecordsSelection = new RecordsSelectionTableAdapter();

        private static readonly RecordsSelectionGroupsTableAdapter taRecordsSelectionGroups =
            new RecordsSelectionGroupsTableAdapter();

        private static readonly RSelTablesCounterTableAdapter taRSelTablesCounter = new RSelTablesCounterTableAdapter();

        private static readonly ConditionTablesRelatedListTableAdapter taConditionTablesRelatedList =
            new ConditionTablesRelatedListTableAdapter();

        private static readonly RecordsSelectionFiltersTableAdapter taRecordsSelectionFilters =
            new RecordsSelectionFiltersTableAdapter();

        private static readonly RecordsSelectionFunctionsTableAdapter taRecordsSelectionFunctions =
            new RecordsSelectionFunctionsTableAdapter();

        private static readonly MappingConditionsTableAdapter taConditions = new MappingConditionsTableAdapter();

        private static readonly MappingConditionsGroupsTableAdapter taConditionsGroups =
            new MappingConditionsGroupsTableAdapter();

        private static readonly ConditionGroupsListTableAdapter taConGroupsList = new ConditionGroupsListTableAdapter();

        private static readonly ConditionsSubGroupsListTableAdapter taConSubGroupsList =
            new ConditionsSubGroupsListTableAdapter();

        private static readonly MappingQValueQuestionsListTableAdapter taMapQValueQuestionsList =
            new MappingQValueQuestionsListTableAdapter();

        // Agnis forms Information table adapters.
        private static readonly FormsListTableAdapter taFormsList = new FormsListTableAdapter();
        private static readonly ModulesListTableAdapter taModulesList = new ModulesListTableAdapter();

        private static readonly NormalizedQuestionsListTableAdapter taNormalizedQuestionsList =
            new NormalizedQuestionsListTableAdapter();

        private static readonly NonNormalizedQuestionsListTableAdapter taNonNormalizedQuestionList =
            new NonNormalizedQuestionsListTableAdapter();

        private static readonly NormalizedValuesListTableAdapter taNormalizedValuesList =
            new NormalizedValuesListTableAdapter();

        private static readonly NonNormalizedValuesListTableAdapter taNonNormalizedValuesList =
            new NonNormalizedValuesListTableAdapter();

        // Agnis objects used during the data translation.
        public static Form aForm = new Form();
        public static FormRevision aFormRevision = new FormRevision();

        #endregion

        /// <summary>
        ///   Set the current connection string for the MappingInfo database.
        /// </summary>
        /// <param name="cString"> </param>
        public static void SetCurrentMappingInfoConnectionString(String cString)
        {
            currentMappingInfoConnectionString = cString;
        }

        /// <summary>
        ///   Load the mapping information from the database. Fill the mapping info objects related to the translation logic.
        /// </summary>
        /// <param name="dsMappingInfo"> </param>
        /// <returns> True if Fill was ok. </returns>
        public static bool LoadMappingInfo(MappingDataSet dsMappingInfo)
        {
            mySqlConnection = new SqlConnection(currentMappingInfoConnectionString);

            taMappingInfo.Connection = mySqlConnection;
            taRecordsSelection.Connection = mySqlConnection;
            taRecordsSelectionGroups.Connection = mySqlConnection;
            taRSelTablesCounter.Connection = mySqlConnection;
            taConditionTablesRelatedList.Connection = mySqlConnection;
            taRecordsSelectionFilters.Connection = mySqlConnection;
            taRecordsSelectionFunctions.Connection = mySqlConnection;
            taConditions.Connection = mySqlConnection;
            taConditionsGroups.Connection = mySqlConnection;
            taConGroupsList.Connection = mySqlConnection;
            taConSubGroupsList.Connection = mySqlConnection;

            //We load the mapping information dataset.
            taMappingInfo.Fill(dsMappingInfo.FieldValues);
            taRecordsSelection.Fill(dsMappingInfo.RecordsSelection);
            taRecordsSelectionGroups.Fill(dsMappingInfo.RecordsSelectionGroups);
            taRSelTablesCounter.Fill(dsMappingInfo.RSelTablesCounter);
            taConditionTablesRelatedList.Fill(dsMappingInfo.ConditionTablesRelatedList);
            taRecordsSelectionFilters.Fill(dsMappingInfo.RecordsSelectionFilters);
            taRecordsSelectionFunctions.Fill(dsMappingInfo.RecordsSelectionFunctions);
            taConditions.Fill(dsMappingInfo.MappingConditions);
            taConditionsGroups.Fill(dsMappingInfo.MappingConditionsGroups);
            taConGroupsList.Fill(dsMappingInfo.ConditionGroupsList);
            taConSubGroupsList.Fill(dsMappingInfo.ConditionsSubGroupsList);
            return true;
        }

        /// <summary>
        ///   Loads the mapping information from the database, corresponding to the information of the Agnis structure.
        /// </summary>
        /// <param name="dsFormInfo"> </param>
        /// <returns> True if Fill was ok. </returns>
        public static bool LoadAgnisFormsInfo(MappingDataSet dsFormInfo)
        {
            mySqlConnection = new SqlConnection(currentMappingInfoConnectionString);

            //We load the Agnis form information tables.
            taFormsList.Connection = mySqlConnection;
            taModulesList.Connection = mySqlConnection;
            taNormalizedQuestionsList.Connection = mySqlConnection;
            taNonNormalizedQuestionList.Connection = mySqlConnection;
            taNormalizedValuesList.Connection = mySqlConnection;
            taNonNormalizedValuesList.Connection = mySqlConnection;
            taMapQValueQuestionsList.Connection = mySqlConnection;

            taFormsList.Fill(dsFormInfo.FormsList);
            taModulesList.Fill(dsFormInfo.ModulesList);
            taNormalizedQuestionsList.Fill(dsFormInfo.NormalizedQuestionsList);
            taNonNormalizedQuestionList.Fill(dsFormInfo.NonNormalizedQuestionsList);
            taNormalizedValuesList.Fill(dsFormInfo.NormalizedValuesList);
            taNonNormalizedValuesList.Fill(dsFormInfo.NonNormalizedValuesList);
            taMapQValueQuestionsList.Fill(dsFormInfo.MappingQValueQuestionsList);
            return true;
        }

        #region Working with Agnis database objects and structure

        /// <summary>
        ///   Gets the list of module rows for the form id send as parameter.
        /// </summary>
        /// <param name="dsFormInfo"> The MappingDataset object from where to retrieve the module info. </param>
        /// <param name="formPublicID"> The form PublicID. </param>
        /// <param name="formVersion"> The form version. </param>
        /// <returns> A dataview object containing the list of modules belonging to the form. </returns>
        public static DataView GetModulesListForThisForm(MappingDataSet dsFormInfo, int formPublicID,
                                                         decimal formVersion)
        {
            DataView modulesListFilter = new DataView(dsFormInfo.ModulesList);
            modulesListFilter.RowFilter = "[caDSR Form Public ID]='" + formPublicID + "' AND "
                                          + "[caDSR Form Version]='" + formVersion + "'";

            if (modulesListFilter.Count > 0)
            {
                return modulesListFilter;
            }
            return null;
        }

        /// <summary>
        ///   Get list of questions belonging to a module.
        /// </summary>
        /// <param name="dsFormInfo"> </param>
        /// <param name="moduleRow"> </param>
        /// <returns> A DataView object containing all the questionsList rows for this module. </returns>
        public static DataView GetQuestionsListForThisModule(MappingDataSet dsFormInfo,
                                                             MappingDataSet.ModulesListRow moduleRow)
        {
            if (moduleRow.Normalized == "Yes") // If normalized
            {
                DataView QuestionsListFilter = new DataView(dsFormInfo.NormalizedQuestionsList);
                QuestionsListFilter.RowFilter = "[Module Public ID]='" + moduleRow.Module_Public_ID + "'";
                if (QuestionsListFilter.Count > 0)
                {
                    return QuestionsListFilter;
                }
                return null;
            }
            else // If non-Normalized
            {
                DataView QuestionsListFilter = new DataView(dsFormInfo.NonNormalizedQuestionsList);
                QuestionsListFilter.RowFilter = "[Module Public ID]='" + moduleRow.Module_Public_ID + "'";
                if (QuestionsListFilter.Count > 0)
                {
                    return QuestionsListFilter;
                }
                return null;
            }
        }

        /// <summary>
        ///   When a module is normalized, it has a MappingQuestion to which all secondary questions are related. This function gets the list of values for the MappingQuestion of this module.
        /// </summary>
        /// <param name="dsFormInfo"> </param>
        /// <param name="moduleRow"> </param>
        /// <returns> A DataView object containing the list of values for this question. </returns>
        public static DataView GetValuesListForThisMappingQuestion(MappingDataSet dsFormInfo,
                                                                   MappingDataSet.ModulesListRow moduleRow)
        {
            DataView normalizedValuesList = new DataView(dsFormInfo.NormalizedValuesList);
            normalizedValuesList.RowFilter = "[CDE Public ID]='" + moduleRow.Mapping_CDE_Public_ID + "' AND"
                                             + "[caDSR Form Public ID]='" + moduleRow.caDSR_Form_Public_ID + "'";

            if (normalizedValuesList.Count > 0)
            {
                return normalizedValuesList;
            }
            return null;
        }

        /// <summary>
        ///   Get the questions list for this normalized module.
        /// </summary>
        /// <param name="dsFormInfo"> </param>
        /// <param name="mappingQValue"> </param>
        /// <returns> A DataView object containing the questionList rows for this module. </returns>
        public static DataView GetQuestionsListForThisNormalizedModule(MappingDataSet dsFormInfo,
                                                                       MappingDataSet.NormalizedValuesListRow
                                                                           mappingQValue)
        {
            DataView qValueQuestionsList = new DataView(dsFormInfo.MappingQValueQuestionsList);
            qValueQuestionsList.RowFilter = "[Module Public ID]='" + mappingQValue.Module_Public_ID + "' AND "
                                            + "[Module Version]='" + mappingQValue.Module_Version + "' AND "
                                            + "[CDE Public ID]='" + mappingQValue.CDE_Public_ID + "' AND "
                                            + "[CDE Version]='" + mappingQValue.CDE_Version + "' AND "
                                            + "[Value Meaning Public ID]='" + mappingQValue.Value_Meaning_Public_ID +
                                            "' AND "
                                            + "[Value Meaning Version]='" + mappingQValue.Value_Meaning_Version + "'";
            if (qValueQuestionsList.Count > 0)
            {
                return qValueQuestionsList;
            }
            return null;
        }

        /// <summary>
        ///   Get the permissible values for this normalized question.
        /// </summary>
        /// <param name="dsFormInfo"> </param>
        /// <param name="questionRow"> </param>
        /// <param name="moduleRow"> </param>
        /// <returns> A DataView object containing all the pValueRows for this question. </returns>
        public static DataView GetPermissibleValuesForThisNormalizedQuestion(MappingDataSet dsFormInfo,
                                                                             MappingDataSet.
                                                                                 MappingQValueQuestionsListRow
                                                                                 questionRow,
                                                                             MappingDataSet.ModulesListRow moduleRow)
        {
            DataView normalizedValuesList = new DataView(dsFormInfo.NormalizedValuesList);
            normalizedValuesList.RowFilter = "[Module Public ID]='" + questionRow.Module_Public_ID + "' AND "
                                             + "[Module Version]='" + questionRow.Module_Version + "' AND "
                                             + "[CDE Public ID]='" + questionRow.CDE_Public_ID_2 + "' AND "
                                             + "[CDE Version]='" + questionRow.CDE_Version_2 + "' AND "
                                             + "[caDSR Form Public ID]='" + moduleRow.caDSR_Form_Public_ID + "'";

            if (normalizedValuesList.Count > 0)
            {
                return normalizedValuesList;
            }
            return null;
        }

        /// <summary>
        ///   Gets the permissible values for this non normalized question.
        /// </summary>
        /// <param name="dsFormInfo"> </param>
        /// <param name="questionRow"> </param>
        /// <param name="moduleRow"> </param>
        /// <returns> A DataView object containing all the pValueRows fro this question. </returns>
        public static DataView GetPermissibleValuesForThisNonNormalizedQuestion(MappingDataSet dsFormInfo,
                                                                                DataRow questionRow,
                                                                                MappingDataSet.ModulesListRow moduleRow)
        {
            DataView nonNormalizedValuesList = new DataView(dsFormInfo.NonNormalizedValuesList);
            nonNormalizedValuesList.RowFilter = "[Module Public ID]='" + questionRow["Module Public ID"] + "' AND "
                                                + "[Module Version]='" + questionRow["Module Version"] + "' AND "
                                                + "[CDE Public ID]='" + questionRow["CDE Public ID"] + "' AND "
                                                + "[CDE Version]='" + questionRow["CDE Version"] + "' AND"
                                                + "[caDSR Form Public ID]='" + moduleRow.caDSR_Form_Public_ID + "'";
            if (nonNormalizedValuesList.Count > 0)
            {
                return nonNormalizedValuesList;
            }
            return null;
        }

        #endregion
    }
}